package xdTool

import sysUtil.ExcelUtil
import sysUtil.PageUtil
import sysUtil.ResultCode
import sysUtil.ResultJson
import sysUtil.SysAuthSQL
import sysUtil.SysCommUtil
import xdInterface.XdController

class XdWidgetPanelController extends XdController{


    def authUtilsService
    def sysDBUtilService
    def index() {}

    @Override
    def detail() {
        return null
    }

    @Override
    def del() {
        return null
    }

    @Override
    def edit() {
        return null
    }

    @Override
    def save() {
        return null
    }

    @Override
    def update() {
        return null
    }

    @Override
    def add() {
        return null
    }
    @Override
    def dowLoad() {  }

    /**
     * 下载panel数据，生成临时文件
     * @return
     */
    def createExcel2007() {
        def result = ResultJson.resultJson()
        try {
            def panelId = Long.parseLong(params.panelId)
            def XdWidgetPanel xdWidgetPanel = XdWidgetPanel.get(panelId)
            params.dataPanelId = xdWidgetPanel.dataPanelId
            params.dataSql = SysCommUtil.Clob2String(xdWidgetPanel.dataSql)
            def sql = sysDBUtilService.getPanelDataSqlByType(xdWidgetPanel.dataType,params)
            def db = sysDBUtilService.getGroovySqlInstanceById(xdWidgetPanel?.dataSourceId)
            //println params.dataSql
            XdTableCfg xdTableCfg = XdTableCfg.get(xdWidgetPanel.dataId)
            def tempFile = ExcelUtil.CREATE_TMPFILE_EXCEL2007(xdTableCfg.getLayUiColsModel(params),sql,db,1,5000,xdTableCfg.page)
            result.put(ResultJson.DATA.name,[tempFile:tempFile,fileName:xdTableCfg.title])
            result.put(ResultJson.CODE.name,ResultCode.OPT_SUCCESS.code)
            result.put(ResultJson.MSG.name,ResultCode.OPT_SUCCESS.message)
        }catch (Exception e){
            result.put(ResultJson.MSG.name,"${e.getStackTrace()}".substring(0,200))
            result.put(ResultJson.CODE.name,ResultCode.OPT_ERROR.code)
        }finally{
            render result
        }
    }

    @Override
    def upLoad() {
        return null
    }

    @Override
    def getOne() {
        return null
    }

    @Override
    def query() {
        return null
    }

    @Override
    def delete() {
        return null
    }

    /**
     * 获取除过自身的panel：用于panel数据集配置
     * @return
     */
    def getPanelExceptSelf(){
        def result = ResultJson.resultJson()

        try {
            def id = Long.parseLong(params?.id)
            def keyword = params?.keyword
            def data  = XdWidgetPanel.findAll("from XdWidgetPanel as a where a.id not in(${id}) ${keyword?" and a.name like '%${keyword}%'":""}",[offset:0,max:10])

            def results = []
            data.each {it ->
                def map = [value:it.id,name:it.name]
                results.add(map)
            }

            result.put(ResultJson.CODE.name,ResultCode.TABLE_DATA_SUCCESS.code)
            result.put(ResultJson.MSG.name,ResultCode.TABLE_DATA_SUCCESS.message)
            result.put(ResultJson.DATA.name,results)
        }catch (Exception e){
            result.put(ResultJson.CODE.name,ResultCode.QUERY_ERROR.code)
            result.put(ResultJson.MSG.name,e.getMessage())
            e.printStackTrace()
        }
        render result
    }

    /**
     * 预览panel数据
     * @return
     */
    def viewPanelData(){
        def result = ResultJson.resultJson()

        try {
            def dataType = params.dataType?:"SQL"
            def dataSourceId = params?.dataSourceId
            def db = sysDBUtilService.getGroovySqlInstanceById(dataSourceId)
            def sql = sysDBUtilService.getPanelDataSqlByType(dataType,params)

            def data = sysDBUtilService.pageResult(sql,0,100,db)
            def cols = [PageUtil.getLayUiColsByResult(data)]
            result.put(ResultJson.CODE.name,ResultCode.TABLE_DATA_SUCCESS.code)
            result.put(ResultJson.MSG.name,ResultCode.TABLE_DATA_SUCCESS.message)
            result.put(ResultJson.COUNT.name,data.size())
            result.put(ResultJson.DATA.name,data)
            result.put("cols",cols)
        }catch (Exception e){
            result.put(ResultJson.CODE.name,ResultCode.QUERY_ERROR.code)
            result.put(ResultJson.MSG.name,e.getMessage())
            e.printStackTrace()
        }
        render result
    }


    /**
     * 预览panel数据
     * @return
     */
    def viewPanelSql(){
        def result = ResultJson.resultJson()

        try {
            def dataType = params.dataType?:"SQL"
            def dataSourceId = params?.dataSourceId
            def sql = sysDBUtilService.getPanelDataSqlByType(dataType,params)
            sql = sql
                    .replaceAll("\\(","<br>(")
                    .replaceAll( /\s[f,F][R,r][o,O][M,m]\s/ ,"<br>from ")
                    .replaceAll(/\s[I,i][N,n][N,n][E,e][R,r]\s/ ,"<br>inner ")
                    .replaceAll(/\s[L,l][E,e][F,f][t]\s/,"<br>left ")
                    .replaceAll(/\s[W,w][h,H][E,e][R,r][E,e]\s/,"<br>where ")
                    .replaceAll(/\s[A,a][n,N][D,d]\s/,"<br>and ")
                    .replaceAll(/\s[o,O][r,R][d,D][e,E][r,R]\s/,"<br>order ")
                    .replaceAll(/\s[G,g][R,r][O,o][u,U][P,p]\s/,"<br>group ")
                    .replaceAll(/\s[U,u][N,n][i,I][o,O][N,n]\s/,"<br>union ")
                    .replaceAll(/\s[S,s][e,E][L,l][e,E][C,c][T,t]\s/,"<br>select ")
            result.put(ResultJson.CODE.name,ResultCode.TABLE_DATA_SUCCESS.code)
            result.put(ResultJson.MSG.name,ResultCode.TABLE_DATA_SUCCESS.message)
            result.put(ResultJson.DATA.name,sql)
        }catch (Exception e){
            result.put(ResultJson.CODE.name,ResultCode.QUERY_ERROR.code)
            result.put(ResultJson.MSG.name,e.getMessage())
            e.printStackTrace()
        }
        render result
    }


    /**
     * 预览panel - sql - 字段
     * @return
     */
    def viewSQLFieldByPanel(){
        def result = ResultJson.resultJson()

        try {
            def panelId = Long.parseLong(params?.id)
            def XdWidgetPanel xdWidgetPanel = XdWidgetPanel.get(panelId)

            //sysDBUtilService.getGroovySqlInstanceById(dataSourceId)
            params.dataPanelId = xdWidgetPanel.dataPanelId
            params.dataSql = SysCommUtil.Clob2String(xdWidgetPanel?.dataSql)
            def sql = sysDBUtilService.getPanelDataSqlByType(xdWidgetPanel.dataType,params)
            def data =  sysDBUtilService.getSqlField(xdWidgetPanel.dataSourceId,sql)
            result.put(ResultJson.CODE.name,ResultCode.TABLE_DATA_SUCCESS.code)
            result.put(ResultJson.MSG.name,ResultCode.TABLE_DATA_SUCCESS.message)
            result.put(ResultJson.DATA.name,data)

        }catch (Exception e){
            result.put(ResultJson.CODE.name,ResultCode.QUERY_ERROR.code)
            result.put(ResultJson.MSG.name,"Field属性：数据集异常!")
            e.printStackTrace()
        }finally{
            render result
        }
    }

    /**
     * 预览最终效果
     * @return
     */
    def viewPanelTableLast(){
        def result = ResultJson.resultJson()

        try {
            def panelId = Long.parseLong(params?.id)
            XdWidgetPanel xdWidgetPanel = XdWidgetPanel.get(panelId)
            XdTableCfg xdTableCfg = XdTableCfg.get(xdWidgetPanel.dataId)
            def db = sysDBUtilService.getGroovySqlInstanceById(xdWidgetPanel.dataSourceId)

            Map  layTable = xdTableCfg.toLayUiTableModel(params)
            def data
            if(xdTableCfg.page){
                layTable.put(ResultJson.URL.name,"../xdWidgetPanel/queryPanelTableData?id=${panelId}")
            }else{
                params.dataPanelId = xdWidgetPanel.dataPanelId
                params.dataSql = SysCommUtil.Clob2String(xdWidgetPanel.dataSql)
                def sql = sysDBUtilService.getPanelDataSqlByType(xdWidgetPanel.dataType,params)
                data = sysDBUtilService.pageAllResult(sql,db)
                layTable.put(ResultJson.DATA.name,data)
            }

            params.panelId = panelId
            def fromItem = authUtilsService.getPanelParamListById(panelId)

            result.put(ResultJson.CODE.name,ResultCode.TABLE_DATA_SUCCESS.code)
            result.put(ResultJson.MSG.name,ResultCode.TABLE_DATA_SUCCESS.message)
            result.put(ResultJson.PANEL.name,SysCommUtil.Clob2String(xdWidgetPanel.html))
            result.put(ResultJson.TABLE.name,layTable)
            result.put(ResultJson.PANEL_ID.name,panelId)
            result.put(ResultJson.FORM_ITEM.name,fromItem)

        }catch (Exception e){
            result.put(ResultJson.CODE.name,ResultCode.QUERY_ERROR.code)
            result.put(ResultJson.MSG.name,e.getMessage())
            e.printStackTrace()
        }
        render result
    }


    /**
     * 查询每张panel对应table的数据=符合layuiTable异步接口
     * @return
     */
    def queryPanelTableData(){
        def result = ResultJson.resultJson()

        try {
            def panelId = Long.parseLong(params?.id)
            def XdWidgetPanel xdWidgetPanel = XdWidgetPanel.get(panelId)

            params.dataPanelId = xdWidgetPanel.dataPanelId
            params.dataSql = SysCommUtil.Clob2String(xdWidgetPanel.dataSql)
            def sql = sysDBUtilService.getPanelDataSqlByType(xdWidgetPanel.dataType,params)

            XdTableCfg xdTableCfg = XdTableCfg.get(xdWidgetPanel.dataId)

            def data= [], count= 0
            def db = sysDBUtilService.getGroovySqlInstanceById(xdWidgetPanel?.dataSourceId)
            if(xdTableCfg.page =="true"){
                def offset = Integer.parseInt(params.page?:"1")
                def max = Integer.parseInt(params.max?:"10")
                data = sysDBUtilService.pageResult(sql,offset,max,db)
                count = sysDBUtilService.pageCount(sql,db)
            }else{
                data = sysDBUtilService.pageAllResult(sql,db)
            }

            result.put(ResultJson.CODE.name,ResultCode.TABLE_DATA_SUCCESS.code)
            result.put(ResultJson.MSG.name,ResultCode.TABLE_DATA_SUCCESS.message)
            result.put(ResultJson.COUNT.name,count)
            result.put(ResultJson.DATA.name,data)
        }catch (Exception e){
            result.put(ResultJson.CODE.name,ResultCode.QUERY_ERROR.code)
            result.put(ResultJson.MSG.name,e.getMessage())
            e.printStackTrace()
        }
        render result
    }

    /**
     * 绑定panel参数
     */
    def cfgPanelParam(){
        def result = ResultJson.resultJson()

        try {

            def flag = String.valueOf(params?.flag)
            if(flag == "add"){//增删改，移动顺序
                def panelId = Long.parseLong(String.valueOf(params?.panelId))
                def paramId = Long.parseLong(String.valueOf(params?.paramId))
                def classFlag = String.valueOf(params?.classFlag)
                XdWidgetPanelParam xdWidgetPanelParam = XdWidgetPanelParam.findByPanelIdAndParamIdAndClassFlag(panelId,paramId,classFlag)

                if(!xdWidgetPanelParam){//不存在则新增
                    def order =  (XdWidgetPanelParam.findAllByPanelId(panelId)?.orderNum?.max()?:0)+1
                    new XdWidgetPanelParam(panelId: panelId,paramId: paramId,orderNum: order,classFlag: classFlag).save(flush: true)
                    //刷新order
                    XdWidgetPanelParam.findAll("from XdWidgetPanelParam as a where a.panelId = :panelId order by a.orderNum",[panelId:panelId]).eachWithIndex {item,index ->
                        item.setOrderNum(index+1)
                    }
                }
            }else if(flag=="del"){

                XdWidgetPanelParam xdWidgetPanelParam
                if(params?.id){
                    def id = Long.parseLong(String.valueOf(params?.id))
                    xdWidgetPanelParam = XdWidgetPanelParam.get(id)
                }else {
                    def panelId = Long.parseLong(String.valueOf(params?.panelId))
                    def paramId = Long.parseLong(String.valueOf(params?.paramId))
                    def classFlag = String.valueOf(params?.classFlag)
                    xdWidgetPanelParam = XdWidgetPanelParam.findByPanelIdAndParamIdAndClassFlag(panelId,paramId,classFlag)
                }

                if(xdWidgetPanelParam){
                    def panelId = xdWidgetPanelParam.panelId
                    xdWidgetPanelParam.delete(flush: true)
                    //刷新order
                    XdWidgetPanelParam.findAll("from XdWidgetPanelParam as a where a.panelId = :panelId order by a.orderNum",[panelId:panelId]).eachWithIndex {item,index ->
                        item.setOrderNum(index+1)
                    }
                }
            }else{//切换顺序
                def id = Long.parseLong(String.valueOf(params?.id)) //XdWidgetPanelParam.id
                XdWidgetPanelParam xdWidgetPanelParam = XdWidgetPanelParam.get(id)
                def min = XdWidgetPanelParam.findAllByPanelId(xdWidgetPanelParam.panelId)?.orderNum?.min()
                def max = XdWidgetPanelParam.findAllByPanelId(xdWidgetPanelParam.panelId)?.orderNum?.max()
                def oldOrder = xdWidgetPanelParam.orderNum
                def newOrder = oldOrder + Integer.parseInt(flag)
                if(newOrder >= min && newOrder <= max){ //顺在范围内
                    XdWidgetPanelParam  oldParam = XdWidgetPanelParam.findByPanelIdAndOrderNum(xdWidgetPanelParam.panelId,newOrder)
                    xdWidgetPanelParam.setOrderNum(newOrder)
                    oldParam.setOrderNum(oldOrder)
                }
            }

            result.put(ResultJson.CODE.name,ResultCode.TABLE_DATA_SUCCESS.code)
            result.put(ResultJson.MSG.name,ResultCode.TABLE_DATA_SUCCESS.message)

        }catch (Exception e){
            result.put(ResultJson.CODE.name,ResultCode.QUERY_ERROR.code)
            result.put(ResultJson.MSG.name,e.getMessage())
            e.printStackTrace()
        }
        render result
    }

    /**
     * list - panel参数
     */
    def listPanelParam(){
        def result = ResultJson.resultJson()
        try {
            def data = sysDBUtilService.pageAllResult(SysAuthSQL.getWidgetPanelParamSQL(params),sysDBUtilService.getGroovySqlInstance())
            result.put(ResultJson.CODE.name,ResultCode.TABLE_DATA_SUCCESS.code)
            result.put(ResultJson.MSG.name,ResultCode.TABLE_DATA_SUCCESS.message)
            result.put(ResultJson.DATA.name,data)
        }catch (Exception e){
            result.put(ResultJson.CODE.name,ResultCode.QUERY_ERROR.code)
            result.put(ResultJson.MSG.name,e.getMessage())
            e.printStackTrace()
        }
        render result
    }
}
